package com.floreantpos.model.dao;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.hibernate.SQLQuery;
import org.hibernate.Session;

import com.floreantpos.model.StoreItemCompare;
import com.floreantpos.model.dao.BaseStoreItemRelDAO;
import com.floreantpos.util.Util;

public class StoreItemRelDAO extends BaseStoreItemRelDAO implements
		com.floreantpos.model.dao.iface.StoreItemRelDAO {

	public StoreItemRelDAO() {
	}

	public List<StoreItemCompare> findAllCompareData(Date from, Date to) {
		Session session = null;
		try {

			session = getSession();

			String sql = "select a.item_name, a.item_count, b.store_name, a.item_count*b.number total, c.numbers storeNumber"
					+ " from (select item_id, item_name, sum(item_count) item_count"
					+ " from ticket_item "
					+ " where modified_time >= cast('"
					+ Util.formatDateTime(from)
					+ "' as TIMESTAMP)"
					+ " and modified_time <= cast('"
					+ Util.formatDateTime(to)
					+ "' as TIMESTAMP)"
					+ " group by item_name, item_id) a"
					+ " left outer join store_item_rel b"
					+ " on a.item_id = b.item_id"
					+ " left outer join store c"
					+ " on b.store_name = c.name"
					+ " where c.modified_time >= cast('"
					+ Util.formatDateTime(from)
					+ "' as TIMESTAMP)"
					+ " and c.modified_time <= cast('"
					+ Util.formatDateTime(to)
					+ "' as TIMESTAMP)";

			SQLQuery sqlQ = session.createSQLQuery(sql);

			List result = sqlQ.list();

			List<StoreItemCompare> ret = new ArrayList<StoreItemCompare>();

			for (int i = 0; i < result.size(); i++) {
				Object[] o = (Object[]) result.get(i);
				StoreItemCompare c = new StoreItemCompare();
				c.setItemName((String) (o[0] != null ? o[0] : ""));
				c.setItemCount((Integer) (o[1] != null ? o[1] : 0));
				c.setStoreName((String) (o[2] != null ? o[2] : ""));
				c.setTotal((Integer) (o[3] != null ? o[3] : 0));
				c.setStoreNumber((Integer) (o[4] != null ? o[4] : 0));
				ret.add(c);
			}

			return ret;
		} finally {
			if (session != null) {
				session.close();
			}
		}
	}
}